package com.apestech.framework.db;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;

import com.apestech.framework.db.callback.QueryCallBackHandler;
import com.apestech.framework.db.callback.ResultValueMapping;
import com.apestech.framework.db.session.SessionUtil;
import com.apestech.framework.dictionary.modul.Table;
import com.apestech.framework.metadata.MetaData;
import com.apestech.framework.metadata.TableInfoCallback;
import com.apestech.framework.sql.condition.ConditionMap;
import com.apestech.framework.util.modul.DBInfo;
import org.apache.log4j.Logger;

import com.apestech.framework.json.JSONArray;
import com.apestech.framework.json.JSONObject;

import javax.sql.RowSet;

/**
 * SQL执行工具，初始化需要带入datasource
 * @author hupo
 *
 */
public class SqlTemplate extends SessionUtil {
	
	private static final Logger logger = Logger.getLogger(SqlTemplate.class);
	private Connection conn;
	private DBInfo dbInfo;

	public SqlTemplate(DBInfo dbInfo) throws Exception {
		this.dbInfo=dbInfo;
	}

	public Connection getConnection() {
		return conn;
	}

	public void setConnection(Connection conn) {
		this.conn = conn;
	}

	public DBInfo getDbInfo() {
		return dbInfo;
	}

	/** 判断数据库是否支持批处理 */
    /*private boolean supportBatch() {
        try {
            return this.dbmd.supportsBatchUpdates();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }
    */

	private Connection getConn() throws Exception {
		Connection cn;
		//if(this.conn!=null){
			cn=conn;
		//} else {
		//	cn=dbInfo.getDataSource().getConnection();
		//}
		return cn;
	}

	private void closeConn(Connection cn) throws Exception {
		if(cn!=conn){
			cn.close();
		}
	}

	public synchronized boolean tableExists(String tableName) throws Exception {
    	return tableExists(tableName, true);
	}

	public synchronized boolean tableExists(String tableName, boolean local) throws Exception {
		MetaData dbmd=null;
		try {
			dbmd=new MetaData(conn, dbInfo.getSchema());
			return dbmd.tableExists(tableName, local);
		} finally {
		}
	}

	public synchronized JSONObject getTableInfo(String tableName) throws Exception {
		MetaData dbmd=null;
		try {
			dbmd=new MetaData(conn, dbInfo.getSchema());
			return dbmd.getTableInfo(tableName);
		} finally {
		}
	}

	public synchronized String getColumnRemark(String colname, String tableName) throws Exception {
		MetaData dbmd=null;
		try {
			dbmd=new MetaData(conn, dbInfo.getSchema());
			return dbmd.getColumnRemark(colname, tableName);
		} finally {
		}
	}

	public synchronized JSONObject getAllTable() throws Exception {
		MetaData dbmd=null;
		try {
			dbmd=new MetaData(conn, dbInfo.getSchema());
			return dbmd.getAllTable();
		} finally {
		}
	}

	public void qryTable(TableInfoCallback tableInfoCallback) throws Exception {
		MetaData dbmd=null;
		try {
			dbmd=new MetaData(conn, dbInfo.getSchema());
			dbmd.qryTable(tableInfoCallback);
		} finally {
		}
	}

	public synchronized boolean functionExists(String tableName) throws Exception {
		MetaData dbmd=null;
		try {
			dbmd=new MetaData(conn, dbInfo.getSchema());
			return dbmd.functionExists(tableName);
		} finally {
		}
	}

	public synchronized boolean procedureExists(String tableName) throws Exception {
		MetaData dbmd=null;
		try {
			dbmd=new MetaData(conn, dbInfo.getSchema());
			return dbmd.procedureExists(tableName);
		} finally {
		}
	}

	public synchronized JSONObject getIndexInfo(String tableName) throws Exception {
		MetaData dbmd=null;
		try {
			dbmd=new MetaData(conn, dbInfo.getSchema());
			return dbmd.getIndexInfo(tableName);
		} finally {
		}
	}

	/**
	 *
	 * @param sql
	 * @param param 内容要求，每个明细MAP中的值为 value,type(VARCHAR2， BIG， DATE)，而且要求数组的顺序与SQL里的？相同
	 * @param queryCallBackHandler 处理查询结果的回调
	 * @throws Exception
	 */
	public void query(String sql, JSONArray param, QueryCallBackHandler queryCallBackHandler, ConditionMap conParam, String timestampField) throws Exception {
		Connection cn=null;
		PreparedStatement psmt=null;
		SqlResultSet sqlrs=null;
		try {
			cn=getConn();
			//psmt=cn.prepareStatement(sql, ResultSet.CONCUR_READ_ONLY);
			psmt=cn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
			psmt.setFetchSize(100);
			if(param!=null && param.size()>0)
				PsmtUtil.setExecPram(psmt, param);
			sqlrs=new SqlResultSet(psmt.executeQuery());
			if(conParam!=null){
				if(conParam.getFormat()!=null && conParam.getFormat().size()>0) sqlrs.setFormat(conParam.getFormat());
				sqlrs.setConParam(conParam);
			}
			sqlrs.setTimestampField(timestampField);
			queryCallBackHandler.getData(sqlrs, null);
		} catch (Exception e) {
			String msg=e.getMessage();
			if(msg==null) msg=e.toString();
			throw new Exception("sql\r\n"+sql+"\r\nerror:"+msg);
		} finally {
			if(sqlrs!=null) sqlrs.close();
			if(psmt!=null) psmt.close();
			closeConn(cn);
		}
	}

	public synchronized ArrayList<String> getSqlColumn(String sql) throws Exception {
		sql=getNoWhereSql(sql);
		Connection cn=null;
		PreparedStatement psmt=null;
		ArrayList<String> list=new ArrayList<String>();
		try {
			cn = getConn();
			psmt = cn.prepareStatement(sql);
			int count = psmt.getMetaData().getColumnCount();
			for (int i = 0; i < count; i++) {
				list.add(psmt.getMetaData().getColumnName(i + 1));
			}
			return list;
		} catch (Exception e){
			throw new Exception("sql\r\n"+sql+"\r\nerror:"+e.getMessage());
		} finally {
			if(psmt!=null) psmt.close();
			closeConn(cn);
		}
	}

	public Table getSqlTable(String sql) throws Exception {
		Connection cn=null;
		PreparedStatement psmt=null;
		Table table=new Table("tempSql");
		try {
			cn=getConn();
			psmt=cn.prepareStatement(sql);
			table.addField(psmt.getMetaData());
			return table;
		} finally {
			if(psmt!=null) psmt.close();
			closeConn(cn);
		}
	}

	public int update(String sql, JSONArray params) throws Exception{
		Connection cn=null;
		PreparedStatement psmt=null;
		try {
			cn = getConn();
			if (params.size() <= 0) {
				return update(sql);
			} else {
				psmt = cn.prepareStatement(sql);
				PsmtUtil.setExecPram(psmt, params);
				return psmt.executeUpdate();
			}
		} catch (Exception e){
			throw e;
		} finally {
			if(psmt!=null) psmt.close();
			closeConn(cn);
		}
	}
	
	public int update(String sql) throws Exception{
		Connection cn=null;
		Statement stmt=null;
		try {
			cn=getConn();
			stmt=cn.createStatement();
			return stmt.executeUpdate(sql);
		} finally {
			if(stmt!=null) stmt.close();
			closeConn(cn);
		}
	}
	
	/**
     * 无参模式批量执行多种不同SQL
     * @param sqls
     * @return
     * @throws Exception
     */
    public int[] batchUpdate(ArrayList<String> sqls) throws Exception{
		Connection cn=null;
		Statement stmt=null;
		try {
			cn=getConn();
			stmt=cn.createStatement();
			int len=sqls.size();
			for(int i=0; i<len; i++){
				String sql=sqls.get(i);
				stmt.addBatch(sql);
			}
			return stmt.executeBatch();
		} finally {
			if(stmt!=null) stmt.close();
			closeConn(cn);
		}
    }
    
    /**
     * 有参模式批量执行多种不同SQL
     * @param sql
     * @param param
     * @return
     * @throws Exception
     */
    public int[] batchUpdate(String sql, ArrayList<JSONArray> param) throws Exception{
		Connection cn=null;
		PreparedStatement psmt=null;
		try {
			cn=getConn();
			int len=param.size();
			if(len<0) return null;
			psmt=cn.prepareStatement(sql);
			for(int i=0; i<len; i++){
				JSONArray list=param.get(i);
				PsmtUtil.setExecPram(psmt, list);
				psmt.addBatch();
			}
			return psmt.executeBatch();
		} finally {
			if(psmt!=null) psmt.close();
			closeConn(cn);
		}
    }

	public void batchUpdate(String sql, SqlResultSet rs, ResultValueMapping resultValueMapping) throws Exception{
		Connection cn=null;
		PreparedStatement psmt=null;
		try {
			cn=getConn();
			psmt=cn.prepareStatement(sql);
			int i=0;
			while(rs.next()){
				i++;
				PsmtUtil.setExecPram(psmt, rs, resultValueMapping);
				psmt.addBatch();
				if(i==5000){
					psmt.executeBatch();
					i=0;
				}
			}
			if(i>0) psmt.executeBatch();
		} finally {
			if(psmt!=null) psmt.close();
			closeConn(cn);
		}
	}

	public void batchUpdate(ArrayList<String> sqls, ArrayList<String> fields, SqlResultSet rs) throws Exception{
    	int len=sqls.size();
		Connection cn=null;
		ArrayList<PreparedStatement> psmts=new ArrayList<PreparedStatement>();
		try {
			cn=getConn();
			for(int i=0; i<len; i++){
				psmts.add(cn.prepareStatement(sqls.get(i)));
			}
			int i=0;
			while(rs.next()){
				i++;
				PsmtUtil.setExecPram(psmts, rs, fields);
				for(int j=0; j<len; j++) {
					psmts.get(j).addBatch();
				}
				if(i==5000){
					for(int j=0; j<len; j++) {
						psmts.get(j).executeBatch();
					}
					i=0;
				}
			}
			if(i>0) {
				for(int j=0; j<len; j++) {
					psmts.get(j).executeBatch();
				}
			}
		} finally {
			if(psmts.size()>0){
				for(int i=0; i<len; i++){
					psmts.get(i).close();
				}
			}
			closeConn(cn);
		}
	}
	
	public void executeDDL(String sql) throws Exception{
		Connection cn=null;
		Statement stmt=null;
		try {
			cn=getConn();
			stmt=cn.createStatement();
			stmt.execute(sql);
		} finally {
			if(stmt!=null) stmt.close();
			closeConn(cn);
		}
	}
	
	/**
	 * 
	 * @param sql
	 * @param params 内容要求，每个明细MAP中的值为value,direction,key,type:(VARCHAR2， BIG， DATE)，而且要求数组的顺序与SQL里的？相同
	 * @return
	 * @throws Exception
	 */
	public JSONObject callProc(String sql, JSONArray params) throws Exception{
		CallableStatement cstm=null;
		Connection cn=null;
		try {
			cn=getConn();
			cstm=cn.prepareCall(sql);
			PsmtUtil.setProcPram(cstm, params);
			cstm.execute();

			/*
			CallableStatement cs = conn.prepareCall("{call proc_Ins_Dept2(?,?,?,?)}");
            cs.setObject(1, 76);
            cs.setObject(2, "市场部");
            cs.setObject(3, "luoyang");
            cs.registerOutParameter(4, java.sql.Types.VARCHAR);
            cs.execute();
            Object objRtn = cs.getObject(4);      //得到返回值
			*/
			return PsmtUtil.getProgOut(cstm, params);
		} finally {
			if(cstm!=null) cstm.close();
			closeConn(cn);
		}
	}
	
	public void callProc(String sql) throws Exception{
		CallableStatement cstm=null;
		Connection cn=null;
		try {
			cn=getConn();
			cstm=cn.prepareCall(sql);
			cstm.execute();
		} finally {
			if(cstm!=null) cstm.close();
			closeConn(cn);
		}
	}

	public int delTabCache(String key) throws Exception{
		PreparedStatement psmt=null;
		Connection cn=null;
		try {
			cn=getConn();
			String sql="DELETE EXE_DICT WHERE KEY=? ";
			psmt = cn.prepareStatement(sql);
			psmt.setString(1, key);
			return psmt.executeUpdate();
		} catch (Exception e){
			throw e;
		} finally {
			if(psmt!=null) psmt.close();
			closeConn(cn);
		}
	}


	public int saveTabCache(String key, byte[] value) throws Exception{
		PreparedStatement psmt=null;
		Connection cn=null;
		try {
			cn=getConn();
			String sql="INSERT INTO EXE_DICT (KEY, VALUE) VALUES (?,?)";
			psmt = cn.prepareStatement(sql);
			psmt.setString(1, key);
			psmt.setBytes(2, value);
			return psmt.executeUpdate();
		} catch (Exception e){
			throw e;
		} finally {
			if(psmt!=null) psmt.close();
			closeConn(cn);
		}
	}

	public byte[] queryTabCache(String key) throws Exception {
		Connection cn=null;
		PreparedStatement psmt=null;
		SqlResultSet sqlrs=null;
		String sql=" SELECT VALUE FROM EXE_DICT WHERE KEY=? ";
		try {
			cn=getConn();
			if(cn.isClosed()){
				System.out.println("");
			}
			psmt=cn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
			psmt.setFetchSize(100);
			psmt.setString(1, key);
			SqlResultSet rs=new SqlResultSet(psmt.executeQuery());
			if(rs.next()){
				return rs.getBytes(1);
			} else {
				return null;
			}
		} catch (Exception e) {
			String msg=e.getMessage();
			if(msg==null) msg=e.toString();
			throw new Exception("sql\r\n"+sql+"\r\nerror:"+msg);
		} finally {
			if(sqlrs!=null) sqlrs.close();
			if(psmt!=null) psmt.close();
			closeConn(cn);
		}
	}

	public boolean hasTabCache(String key) throws Exception {
		Connection cn=null;
		PreparedStatement psmt=null;
		SqlResultSet sqlrs=null;
		String sql=" SELECT 1 FROM EXE_DICT WHERE KEY=? ";
		try {
			cn=getConn();
			psmt=cn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
			psmt.setFetchSize(100);
			psmt.setString(1, key);
			SqlResultSet rs=new SqlResultSet(psmt.executeQuery());
			if(rs.next()){
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			String msg=e.getMessage();
			if(msg==null) msg=e.toString();
			throw new Exception("sql\r\n"+sql+"\r\nerror:"+msg);
		} finally {
			if(sqlrs!=null) sqlrs.close();
			if(psmt!=null) psmt.close();
			closeConn(cn);
		}
	}
	
}
